﻿/*
Created		14/10/2013
Modified		07/05/2014
Project		
Model			
Company		
Author		
Version		
Database		MS SQL 2005 
*/


Create table [MonHoc]
(
	[MaMonHoc] Nvarchar(20) NOT NULL,
	[TenMonHoc] Nvarchar(100) NULL,
	[SoTietLiThuyet] Integer NULL,
	[SoTietThucHanh] Integer NULL,
	[SoTietThaoLuan] Integer NULL,
	[SoTietTuHoc] Integer NULL,
	[TongSoTiet] Integer NULL,
	[SoDVHT] Integer NULL,
	[HinhThucThi] Nvarchar(100) NULL,
	[MaBoMon] Nvarchar(20) NOT NULL,
Primary Key ([MaMonHoc])
) 
go

Create table [HocVien]
(
	[MaHocVien] Nvarchar(20) NOT NULL,
	[HoTen] Nvarchar(50) NULL,
	[DiaChi] Nvarchar(200) NULL,
	[NgaySinh] Smalldatetime NULL,
	[GioiTinh] Nvarchar(10) NULL,
	[CapBac] Nvarchar(20) NULL,
	[MaLop] Nvarchar(20) NOT NULL,
Primary Key ([MaHocVien])
) 
go

Create table [BoMon]
(
	[MaBoMon] Nvarchar(20) NOT NULL,
	[TenBoMon] Nvarchar(100) NULL,
	[MaKhoa] Nvarchar(20) NOT NULL,
Primary Key ([MaBoMon])
) 
go

Create table [Khoa]
(
	[MaKhoa] Nvarchar(20) NOT NULL,
	[TenKhoa] Nvarchar(100) NULL,
Primary Key ([MaKhoa])
) 
go

Create table [TieuDoan]
(
	[MaTieuDoan] Nvarchar(20) NOT NULL,
	[TenTieuDoan] Nvarchar(50) NULL,
Primary Key ([MaTieuDoan])
) 
go

Create table [DaiDoi]
(
	[MaDaiDoi] Nvarchar(20) NOT NULL,
	[TenDaiDoi] Nvarchar(50) NULL,
	[MaTieuDoan] Nvarchar(20) NOT NULL,
Primary Key ([MaDaiDoi])
) 
go

Create table [Lop]
(
	[MaLop] Nvarchar(20) NOT NULL,
	[TenLop] Nvarchar(50) NULL,
	[KhoaHoc] Integer NULL,
	[NamThanhLap] Smalldatetime NULL,
	[MaDaiDoi] Nvarchar(20) NOT NULL,
	[MaTrinhDo] Nvarchar(10) NOT NULL,
	[MaHeDaoTao] Nvarchar(20) NOT NULL,
	[MaChuyenNganh] Nvarchar(20) NOT NULL,
Primary Key ([MaLop])
) 
go

Create table [GiaoVien]
(
	[MaGiaoVien] Nvarchar(20) NOT NULL,
	[TenGiaoVien] Nvarchar(50) NULL,
	[NgaySinh] Smalldatetime NULL,
	[GioiTinh] Nvarchar(10) NULL,
	[HocVi] Nvarchar(20) NULL,
	[CapBac] Nvarchar(20) NULL,
	[MaBoMon] Nvarchar(20) NOT NULL,
Primary Key ([MaGiaoVien])
) 
go

Create table [ChucVu]
(
	[MaChucVu] Nvarchar(20) NOT NULL,
	[TenChucVu] Nvarchar(50) NULL,
Primary Key ([MaChucVu])
) 
go

Create table [ChuyenNganh]
(
	[MaChuyenNganh] Nvarchar(20) NOT NULL,
	[TenChuyenNganh] Nvarchar(100) NULL,
	[MaKhoa] Nvarchar(20) NOT NULL,
Primary Key ([MaChuyenNganh])
) 
go

Create table [HocKiNamHoc]
(
	[MaHocKi] Nvarchar(20) NOT NULL,
	[TenHocKi] Nvarchar(20) NULL,
	[TenNamHoc] Nvarchar(20) NULL,
	[TuNgay] Smalldatetime NULL,
	[DenNgay] Smalldatetime NULL,
Primary Key ([MaHocKi])
) 
go

Create table [HeDaoTao]
(
	[MaHeDaoTao] Nvarchar(20) NOT NULL,
	[TenHeDaoTao] Nvarchar(50) NULL,
Primary Key ([MaHeDaoTao])
) 
go

Create table [TrinhDoDaoTao]
(
	[MaTrinhDo] Nvarchar(10) NOT NULL,
	[TenTrinhDo] Nvarchar(20) NULL,
Primary Key ([MaTrinhDo])
) 
go

Create table [CTChucVu]
(
	[MaGiaoVien] Nvarchar(20) NOT NULL,
	[MaChucVu] Nvarchar(20) NOT NULL,
	[NgayNhanChuc] Smalldatetime NULL,
Primary Key ([MaGiaoVien],[MaChucVu])
) 
go

Create table [LichHoc]
(
	[MaMonHoc] Nvarchar(20) NOT NULL,
	[MaLop] Nvarchar(20) NOT NULL,
	[MaHocKi] Nvarchar(20) NOT NULL,
	[MaGiaoVien] Nvarchar(20) NOT NULL,
	[NgayBatDau] Smalldatetime NULL,
	[NgayKetThuc] Smalldatetime NULL,
	[NgayThi] Smalldatetime NULL,
Primary Key ([MaMonHoc],[MaLop],[MaHocKi])
) 
go

Create table [DiemThiMonHoc]
(
	[MaHocVien] Nvarchar(20) NOT NULL,
	[MaMonHoc] Nvarchar(20) NOT NULL,
	[MaLop] Nvarchar(20) NOT NULL,
	[MaHocKi] Nvarchar(20) NOT NULL,
	[DiemQuaTrinh] Float NULL,
	[DiemThi] Float NULL,
	[DiemKetThuc] Float NULL,
Primary Key ([MaHocVien],[MaMonHoc],[MaLop],[MaHocKi])
) 
go

Create table [DiemRenLuyenThang]
(
	[STT] Char(1) NOT NULL,
	[MaHocVien] Nvarchar(20) NOT NULL,
	[Thang] Integer NULL,
	[Nam] Integer NULL,
	[Diem] Float NULL,
	[XepLoai] Char(1) NULL,
Primary Key ([STT])
) 
go


Alter table [LichHoc] add  foreign key([MaMonHoc]) references [MonHoc] ([MaMonHoc])  on update no action on delete no action 
go
Alter table [DiemThiMonHoc] add  foreign key([MaHocVien]) references [HocVien] ([MaHocVien])  on update no action on delete no action 
go
Alter table [DiemRenLuyenThang] add  foreign key([MaHocVien]) references [HocVien] ([MaHocVien])  on update no action on delete no action 
go
Alter table [GiaoVien] add  foreign key([MaBoMon]) references [BoMon] ([MaBoMon])  on update no action on delete no action 
go
Alter table [MonHoc] add  foreign key([MaBoMon]) references [BoMon] ([MaBoMon])  on update no action on delete no action 
go
Alter table [BoMon] add  foreign key([MaKhoa]) references [Khoa] ([MaKhoa])  on update no action on delete no action 
go
Alter table [ChuyenNganh] add  foreign key([MaKhoa]) references [Khoa] ([MaKhoa])  on update no action on delete no action 
go
Alter table [DaiDoi] add  foreign key([MaTieuDoan]) references [TieuDoan] ([MaTieuDoan])  on update no action on delete no action 
go
Alter table [Lop] add  foreign key([MaDaiDoi]) references [DaiDoi] ([MaDaiDoi])  on update no action on delete no action 
go
Alter table [HocVien] add  foreign key([MaLop]) references [Lop] ([MaLop])  on update no action on delete no action 
go
Alter table [LichHoc] add  foreign key([MaLop]) references [Lop] ([MaLop])  on update no action on delete no action 
go
Alter table [CTChucVu] add  foreign key([MaGiaoVien]) references [GiaoVien] ([MaGiaoVien])  on update no action on delete no action 
go
Alter table [LichHoc] add  foreign key([MaGiaoVien]) references [GiaoVien] ([MaGiaoVien])  on update no action on delete no action 
go
Alter table [CTChucVu] add  foreign key([MaChucVu]) references [ChucVu] ([MaChucVu])  on update no action on delete no action 
go
Alter table [Lop] add  foreign key([MaChuyenNganh]) references [ChuyenNganh] ([MaChuyenNganh])  on update no action on delete no action 
go
Alter table [LichHoc] add  foreign key([MaHocKi]) references [HocKiNamHoc] ([MaHocKi])  on update no action on delete no action 
go
Alter table [Lop] add  foreign key([MaHeDaoTao]) references [HeDaoTao] ([MaHeDaoTao])  on update no action on delete no action 
go
Alter table [Lop] add  foreign key([MaTrinhDo]) references [TrinhDoDaoTao] ([MaTrinhDo])  on update no action on delete no action 
go
Alter table [DiemThiMonHoc] add  foreign key([MaMonHoc],[MaLop],[MaHocKi]) references [LichHoc] ([MaMonHoc],[MaLop],[MaHocKi])  on update no action on delete no action 
go


Set quoted_identifier on
go


Set quoted_identifier off
go


